OpenEdge Data Management:<br />DataServer for ORACLE Data types

ORACLE data types differ from OpenEdge data types. However, each ORACLE internal data type supported by the DataServer has at least one OpenEdge equivalent.

The DataServer translates the ORACLE data types into OpenEdge equivalents. When an ORACLE data type has more than one OpenEdge equivalent, the DataServer supplies a default data type. The schema image contains the OpenEdge data definitions for the ORACLE columns, which you can modify by using the Data Dictionary. For example, the DataServer assigns the NUMBER data type the OpenEdge equivalent, DECIMAL. You can then change the data type from DECIMAL to INTEGER or LOGICAL. See the "Modifying a schema image" section for an explanation of how to change OpenEdge data types in the schema image.

ORACLE allows users to define their own data types, known as external data types. ORACLE converts these external types to an equivalent internal type. For example, a float data type maps to NUMBER. The DataServer also considers it to be a NUMBER and maps it to DECIMAL in the schema image.

Table 2–5 lists the ORACLE internal data types supported by the DataServer and their OpenEdge equivalents. The table also shows the default equivalent supplied by the DataServer for those ORACLE data types with more than one OpenEdge equivalent. The sections directly following Table 2–5 provide additional details about several of the ORACLE data types and each data type’s OpenEdge equivalent.

Table 2–5: ORACLE and OpenEdge data types
ORACLE data type	OpenEdge equivalent
`CHAR`	`CHARACTER`
`VARCHAR2`	`CHARACTER`
`NUMBER`	`DECIMAL` `INTEGER LOGICAL`
`DATE`	`DATE INTEGER`
`LONG`	`CHARACTER`
`RAW` (limited support)	`RAW`
`LONG RAW`	`RAW`
`BLOBBFILE`	`BLOB`

ORACLE Char and Varchar2 data types

The VARCHAR2 data type does not pad data with trailing spaces. However, ORACLE CHAR does pad with trailing spaces. For example, in a CHAR column 20 characters wide, the entry MA includes the two characters and 18 spaces. Your application will find the entry only if a WHERE clause searches for the string that includes MA and the 18 spaces. If the column is a VARCHAR2 column, your application will find the entry if it searches for the two characters. The VARCHAR2 data type is more consistent with the Progress CHARACTER data type.

ORACLE Numeric data type

ORACLE has only one numeric data type, which the DataServer translates to an OpenEdge DECIMAL, INTEGER, or LOGICAL data type depending on the scale and precision of the NUMBER column. However, OpenEdge handles the OpenEdge INTEGER data type more efficiently than it does a DECIMAL data type. You can use the OpenEdge Data Dictionary to change the data type from DECIMAL to INTEGER in the schema image. See the "Modifying a schema image" section in Chapter 7, "The Data Server Tutorial," for instructions.

ORACLE Number data

Consider the local version of the ORACLE database when accessing NUMBER data. The internal radix (decimal point symbol) varies among versions. For example, some European versions expect the radix to be a comma (,) rather than a period (.). The DataServer issues an ALTER SESSION SET SEPARATOR statement, which might result in stored procedures that you call from the Progress 4GL seeing a different radix separator.

Logical data type and ORACLE equivalents

ORACLE does not have a LOGICAL data type. You can change the data type for a field from DECIMAL to LOGICAL in the schema holder. OpenEdge then reads the numeric values stored in the ORACLE column, as Table 2–6 shows.

Table 2–6: LOGICAL data type and ORACLE equivalents
OpenEdge	ORACLE
True	Any nonzero value
False	0

The DataServer stores the contents of an OpenEdge LOGICAL data type in an ORACLE NUMBER column as:

If you retain values other than 1 or 0 in the ORACLE column, do not write a value to that column as a LOGICAL data type.

ORACLE Date data type

The ORACLE DATE data type contains both date and time information. By default, in the schema image, an ORACLE date column is represented by two OpenEdge fields: a DATE field and an INTEGER field. The DataServer follows this convention when naming the fields: column column-1. For example, an ORACLE date column named Date_Due converts to two fields named Date_Due and Date_Due-1 in the schema image. Date_Due is a DATE field and Date_Due-1 is an INTEGER field.

OpenEdge converts the time component of the ORACLE date to an INTEGER value. To convert the INTEGER value into time, use the Progress 4GL STRING and TIME functions, as described in OpenEdge Development: Progress 4GL Reference .

You can change the data type mapping for DATE to CHARACTER in the schema image using the Data Dictionary. If you change the mapping, you must remove the column representing the time as an INTEGER. Use this feature only if you must use the time portion of an ORACLE DATE in WHERE clauses.

ORACLE Date data type

The range of ORACLE DATE are the years 4712 B.C. to 4712 A.D. The range of DATE that the DataServer supports are the years 999 B.C. to 9999 A.D. The DataServer converts all years greater than 4712 to 4712. For example, if your OpenEdge application updates a DATE column with the year 4750, the DataServer converts it to 4712 before writing it to the ORACLE database.

Progress 4GL supports Raw data type

ORACLE Blob and Bfile data types

The ORACLE BLOB and BFILE data types are used to store blocks of unstructured data. Database columns of these types store locators. For the BLOB data type, the data is stored within the database. For the BFILE, the data is stored outside of ORACLE as an operating system file. For this reason, the BFILE data type is read-only, and cannot be modified or participate in a transaction. For more information on how ORACLE defines these data types, see your ORACLE documentation. For information about programming using the BLOB data type, see OpenEdge Development: Progress 4GL Handbook . For information about the specific statements and functions, see OpenEdge Development: Progress 4GL Reference .